package com.huaxia.dao.attend;

import com.huaxia.pojo.attend.BranchLayerAttend;
import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/03 14:26
 */

public interface BranchLayerAttendMapper {

    @Select("select a.branchsname,a.branchBcSignNum,b.branchBcNum,round(decode(b.branchBcNum,0,-9999,a.branchBcSignNum*100/b.branchBcNum),1)branchBcYield,c.branchBmSignNum,d.branchBmNum,round(decode(d.branchBmNum,0,-9999,c.branchBmSignNum*100/d.branchBmNum),1)branchBmYield from\n" +
            "(select sb.branchsname,NVL(a.num,0)branchBcSignNum from\n" +
            "(select a.branchcode,count(a.branchcode)num from\n" +
            "(select a.branchcode,a.agentcode from\n" +
            "(select t.branchcode,t.agentcode,t.agentgrade from D_AGENT_PROVINCE_YX2 t,\n" +
            "(select t.staffcode from LTATTENDANCE t where to_char(t.attendate,'yyyy/mm/dd')=to_char(sysdate,'yyyy/mm/dd') \n" +
            "and t.staffcode is not null group by t.staffcode,t.managecom)y where t.agentcode=y.staffcode and t.agentstate='在职')a where a.agentgrade in('SBC','BC'))a group by a.branchcode)a right join SD_BRANCH sb on sb.branchcode=a.branchcode\n" +
            ")a,\n" +
            "(select sb.branchsname,NVL(a.num,0)branchBcNum from\n" +
            "(select a.branchcode,count(a.branchcode)num from \n" +
            "(select t.branchcode,t.agentcode from D_AGENT_PROVINCE_YX2 t where t.agentgrade in('SBC','BC','AS','SAS') and t.agentstate='在职'\n" +
            ")a group by a.branchcode)a right join SD_BRANCH sb on sb.branchcode=a.branchcode\n" +
            ")b,\n" +
            "(select sb.branchsname,NVL(a.num,0)branchBmSignNum from\n" +
            "(select a.branchcode,count(a.branchcode)num from\n" +
            "(select a.branchcode,a.agentcode from\n" +
            "(select t.branchcode,t.agentcode,t.agentgrade from D_AGENT_PROVINCE_YX2 t,\n" +
            "(select t.staffcode from LTATTENDANCE t where to_char(t.attendate,'yyyy/mm/dd')=to_char(sysdate,'yyyy/mm/dd') \n" +
            "and t.staffcode is not null group by t.staffcode,t.managecom)y where t.agentcode=y.staffcode and t.agentstate='在职')a where a.agentgrade in('SBM','BM'))a group by a.branchcode)a right join SD_BRANCH sb on sb.branchcode=a.branchcode\n" +
            ")c,\n" +
            "(select sb.branchsname,NVL(a.num,0)branchBmNum from\n" +
            "(select a.branchcode,count(a.branchcode)num from \n" +
            "(select t.branchcode,t.agentcode from D_AGENT_PROVINCE_YX2 t where t.agentgrade in('SBM','BM','AS','SAS') and t.agentstate='在职'\n" +
            ")a group by a.branchcode)a right join SD_BRANCH sb on sb.branchcode=a.branchcode\n" +
            ")d\n" +
            "where a.branchsname=b.branchsname and b.branchsname=c.branchsname and c.branchsname=d.branchsname order by branchBcYield DESC")
    List<BranchLayerAttend> getBranchLayerAttend();
}
